IF EXISTS (SELECT * FROM sysobjects WHERE name = 'HRGetClients' AND user_name(uid) = 'dbo')
	DROP PROCEDURE [dbo].[HRGetClients];
GO

CREATE PROCEDURE [dbo].[HRGetClients]
AS
	SET NOCOUNT ON;
	SELECT ClientID, FullName, Gender, InOffice, Comment, Address, Phone, BirthDate, Email FROM Client WHERE (Deleted = 0);
GO



IF EXISTS (SELECT * FROM sysobjects WHERE name = 'HRAddClient' AND user_name(uid) = 'dbo')
	DROP PROCEDURE [dbo].[HRAddClient];
GO

CREATE PROCEDURE [dbo].[HRAddClient]
(
	@FullName nvarchar(128),
	@Gender char(1),
	@InOffice tinyint,
	@Comment nvarchar(512),
	@Address nvarchar(512),
	@Phone nvarchar(128),
	@BirthDate nvarchar(128),
	@Email nvarchar(512)
)
AS
	SET NOCOUNT OFF;
	INSERT INTO Client(FullName, Gender, InOffice, Comment, Address, Phone, BirthDate, Email) VALUES (@FullName, @Gender, @InOffice, @Comment, @Address, @Phone, @BirthDate, @Email);
	SELECT ClientID, Gender, FullName, InOffice, Comment, Address, Phone, BirthDate, Email FROM Client WHERE (ClientID = @@IDENTITY);
GO



IF EXISTS (SELECT * FROM sysobjects WHERE name = 'HRUpdateClient' AND user_name(uid) = 'dbo')
	DROP PROCEDURE [dbo].[HRUpdateClient];
GO

CREATE PROCEDURE [dbo].[HRUpdateClient]
(
	@FullName nvarchar(128),
	@Gender char(1),
	@InOffice tinyint,
	@Comment nvarchar(512),
	@Address nvarchar(512),
	@Phone nvarchar(128),
	@BirthDate nvarchar(128),
	@Email nvarchar(512),
	@Original_ClientID int,
	@ClientID int
)
AS
	SET NOCOUNT OFF;
	UPDATE Client SET FullName = @FullName, Gender = @Gender, InOffice = @InOffice, Comment = @Comment, Address = @Address, Phone = @Phone, BirthDate = @BirthDate, Email = @Email WHERE (ClientID = @Original_ClientID);
	SELECT ClientID, Gender, FullName, InOffice, Comment, Address, Phone, BirthDate, Email FROM Client WHERE (ClientID = @ClientID);
GO



IF EXISTS (SELECT * FROM sysobjects WHERE name = 'HRDeleteClient' AND user_name(uid) = 'dbo')
	DROP PROCEDURE [dbo].[HRDeleteClient];
GO

CREATE PROCEDURE [dbo].[HRDeleteClient]
(
	@Original_ClientID int
)
AS
	SET NOCOUNT OFF;
	UPDATE Client SET Deleted=1 WHERE (ClientID = @Original_ClientID);
	UPDATE Appointment SET Deleted=1 WHERE (ClientID = @Original_ClientID);
GO


-------- BILL STORED PROCS ---------------


IF EXISTS (SELECT name FROM   sysobjects WHERE  name = N'HRGetBillDetails'  AND 	  type = 'P')
DROP PROCEDURE HRGetBillDetails
GO


CREATE PROCEDURE HRGetBillDetails 
	@ClientID int
AS
	SELECT 
		b.BillID, 
		'S' as Category, 
		a.ServiceCost as Cost, 
		s.Label as Info
	FROM Bill b
	INNER JOIN Appointment a on 
			a.BillID=b.BillID
	INNER JOIN Service s on  
			s.ServiceID=a.ServiceID
	WHERE
		a.ClientID=@ClientID

	UNION ALL

	SELECT 
		b.BillID, 
		'P' as Category, 
		bp.Cost as Cost, 
		p.Label as Info
	FROM Bill b
	INNER JOIN BillProduct bp on 
			bp.BillID=b.BillID
	INNER JOIN Product p on  
			p.ProductID=bp.ProductID
	WHERE
		b.ClientID=@ClientID

GO


-------- EMPLOYEE STORED PROCS ---------------
IF EXISTS (SELECT * FROM sysobjects WHERE name = 'HRGetEmployee' AND user_name(uid) = 'dbo')
	DROP PROCEDURE [dbo].[HRGetEmployee];
GO

CREATE PROCEDURE [dbo].[HRGetEmployee]
AS
	SET NOCOUNT ON;
SELECT EmployeeID, FullName, Position, MasterLevelID, SecurityLevel, Phone, Address, Comments, Password, Sys, IsOperator, IsAttendant, Photo FROM Employee WHERE (Deleted = 0);
GO



IF EXISTS (SELECT * FROM sysobjects WHERE name = 'HRAddEmployee' AND user_name(uid) = 'dbo')
	DROP PROCEDURE [dbo].[HRAddEmployee];
GO

CREATE PROCEDURE [dbo].[HRAddEmployee]
(
	@FullName nvarchar(128),
	@Position nvarchar(128),
	@MasterLevelID int,
	@SecurityLevel int,
	@Phone nvarchar(128),
	@Address nvarchar(512),
	@Comments nvarchar(2048),
	@Password nvarchar(64),
	@Sys bit,
	@IsOperator bit,
	@IsAttendant bit,
	@Photo image
)
AS
	SET NOCOUNT OFF;
INSERT INTO Employee(FullName, Position, MasterLevelID, SecurityLevel, Phone, Address, Comments, Password, Sys, IsOperator, IsAttendant, Photo) VALUES (@FullName, @Position, @MasterLevelID, @SecurityLevel, @Phone, @Address, @Comments, @Password, @Sys, @IsOperator, @IsAttendant, @Photo);
	SELECT EmployeeID, FullName, Position, MasterLevelID, SecurityLevel, Phone, Address, Comments, Password, Sys, IsOperator, IsAttendant, Photo FROM Employee WHERE (EmployeeID = @@IDENTITY);
GO



IF EXISTS (SELECT * FROM sysobjects WHERE name = 'HRUpdateEmployee' AND user_name(uid) = 'dbo')
	DROP PROCEDURE [dbo].[HRUpdateEmployee];
GO

CREATE PROCEDURE [dbo].[HRUpdateEmployee]
(
	@FullName nvarchar(128),
	@Position nvarchar(128),
	@MasterLevelID int,
	@SecurityLevel int,
	@Phone nvarchar(128),
	@Address nvarchar(512),
	@Comments nvarchar(2048),
	@Password nvarchar(64),
	@Sys bit,
	@IsOperator bit,
	@IsAttendant bit,
	@Photo image,
	@Original_EmployeeID int,
	@EmployeeID int
)
AS
	SET NOCOUNT OFF;
	UPDATE Employee SET FullName = @FullName, Position = @Position, MasterLevelID = @MasterLevelID, SecurityLevel = @SecurityLevel, Phone = @Phone, Address = @Address, Comments = @Comments, Password = @Password, Sys = @Sys, IsOperator = @IsOperator, IsAttendant = @IsAttendant, Photo = @Photo WHERE (EmployeeID = @Original_EmployeeID);
	SELECT EmployeeID, FullName, Position, MasterLevelID, SecurityLevel, Phone, Address, Comments, Password, Sys, IsOperator, IsAttendant, Photo FROM Employee WHERE (EmployeeID = @EmployeeID);
GO



IF EXISTS (SELECT * FROM sysobjects WHERE name = 'HRDeleteEmployee' AND user_name(uid) = 'dbo')
	DROP PROCEDURE [dbo].[HRDeleteEmployee];
GO

CREATE PROCEDURE [dbo].[HRDeleteEmployee]
(
	@Original_EmployeeID int
)
AS
	SET NOCOUNT OFF;
	UPDATE Employee SET Deleted=1 WHERE (EmployeeID = @Original_EmployeeID);
	UPDATE Appointment SET Deleted=1 WHERE (PerformerID = @Original_EmployeeID);
	DELETE Schedule WHERE EmployeeID=@Original_EmployeeID AND (NOT ExactDate IS NULL)
GO


------------------- PRODUCT STORED PROCS --------------------
IF EXISTS (SELECT * FROM sysobjects WHERE name = 'HRGetProduct' AND user_name(uid) = 'dbo')
	DROP PROCEDURE [dbo].[HRGetProduct];
GO

CREATE PROCEDURE [dbo].[HRGetProduct]
AS
	SET NOCOUNT ON;
SELECT ProductID, Label, Price, Type, Family, Stock, CriticalStock, Comment FROM Product WHERE (Deleted = 0);
GO



IF EXISTS (SELECT * FROM sysobjects WHERE name = 'HRAddProduct' AND user_name(uid) = 'dbo')
	DROP PROCEDURE [dbo].[HRAddProduct];
GO

CREATE PROCEDURE [dbo].[HRAddProduct]
(
	@Label nvarchar(128),
	@Price money,
	@Type nvarchar(128),
	@Family nvarchar(128),
	@Stock int,
	@CriticalStock int,
	@Comment nvarchar(512)
)
AS
	SET NOCOUNT OFF;
INSERT INTO Product(Label, Price, Type, Family, Stock, CriticalStock, Comment) VALUES (@Label, @Price, @Type, @Family, @Stock, @CriticalStock, @Comment);
	SELECT ProductID, Label, Price, Type, Family, Stock, CriticalStock, Comment FROM Product WHERE (ProductID = @@IDENTITY);
GO



IF EXISTS (SELECT * FROM sysobjects WHERE name = 'HRUpdateProduct' AND user_name(uid) = 'dbo')
	DROP PROCEDURE [dbo].[HRUpdateProduct];
GO

CREATE PROCEDURE [dbo].[HRUpdateProduct]
(
	@Label nvarchar(128),
	@Price money,
	@Type nvarchar(128),
	@Family nvarchar(128),
	@Stock int,
	@CriticalStock int,
	@Comment nvarchar(512),
	@Original_ProductID int,
	@ProductID int
)
AS
	SET NOCOUNT OFF;
UPDATE Product SET Label = @Label, Price = @Price, Type = @Type, Family = @Family, Stock = @Stock, CriticalStock = @CriticalStock, Comment = @Comment WHERE (ProductID = @Original_ProductID);
	SELECT ProductID, Label, Price, Type, Family, Stock, CriticalStock, Comment FROM Product WHERE (ProductID = @ProductID);
GO



IF EXISTS (SELECT * FROM sysobjects WHERE name = 'HRDeleteProduct' AND user_name(uid) = 'dbo')
	DROP PROCEDURE [dbo].[HRDeleteProduct];
GO

CREATE PROCEDURE [dbo].[HRDeleteProduct]
(
	@Original_ProductID int
)
AS
	SET NOCOUNT OFF;
	UPDATE Product SET Deleted=1 WHERE (ProductID = @Original_ProductID);
GO


------------- SERVICE STORED PROCS ------------------
IF EXISTS (SELECT * FROM sysobjects WHERE name = 'HRGetService' AND user_name(uid) = 'dbo')
	DROP PROCEDURE [dbo].[HRGetService];
GO

CREATE PROCEDURE [dbo].[HRGetService]
AS
	SET NOCOUNT ON;
	SELECT ServiceID, Label, Type, Comment, Color, TextColor FROM Service WHERE (Deleted = 0);
GO



IF EXISTS (SELECT * FROM sysobjects WHERE name = 'HRAddService' AND user_name(uid) = 'dbo')
	DROP PROCEDURE [dbo].[HRAddService];
GO

CREATE PROCEDURE [dbo].[HRAddService]
(
	@Label nvarchar(128),
	@Type nvarchar(128),
	@Comment nvarchar(512),
	@Color int,
	@TextColor int
)
AS
	SET NOCOUNT OFF;
	INSERT INTO Service(Label, Type, Comment, Color, TextColor) VALUES (@Label, @Type, @Comment, @Color, @TextColor);
	SELECT ServiceID, Label, Type, Comment, Color, TextColor FROM Service WHERE (ServiceID = @@IDENTITY);
GO



IF EXISTS (SELECT * FROM sysobjects WHERE name = 'HRUpdateService' AND user_name(uid) = 'dbo')
	DROP PROCEDURE [dbo].[HRUpdateService];
GO

CREATE PROCEDURE [dbo].[HRUpdateService]
(
	@Label nvarchar(128),
	@Type nvarchar(128),
	@Comment nvarchar(512),
	@Color int,
	@TextColor int,
	@Original_ServiceID int,
	@ServiceID int
)
AS
	SET NOCOUNT OFF;
	UPDATE Service SET Label = @Label, Type = @Type, Comment = @Comment, Color=@Color, TextColor=@TextColor WHERE (ServiceID = @Original_ServiceID);
	SELECT ServiceID, Label, Type, Comment, Color, TextColor FROM Service WHERE (ServiceID = @ServiceID);
GO



IF EXISTS (SELECT * FROM sysobjects WHERE name = 'HRDeleteService' AND user_name(uid) = 'dbo')
	DROP PROCEDURE [dbo].[HRDeleteService];
GO

CREATE PROCEDURE [dbo].[HRDeleteService]
(
	@Original_ServiceID int
)
AS
	SET NOCOUNT OFF;
	UPDATE Service SET Deleted=1 WHERE (ServiceID = @Original_ServiceID);
	UPDATE Appointment SET Deleted=1 WHERE (ServiceID = @Original_ServiceID);
GO



-------------- MASTERLEVEL STORED PROCS --------------------------------
IF EXISTS (SELECT * FROM sysobjects WHERE name = 'HRGetMasterLevel' AND user_name(uid) = 'dbo')
	DROP PROCEDURE [dbo].[HRGetMasterLevel];
GO

CREATE PROCEDURE [dbo].[HRGetMasterLevel]
AS
	SET NOCOUNT ON;
	SELECT MasterLevelID, LevelName FROM MasterLevel WHERE Deleted=0;
GO



IF EXISTS (SELECT * FROM sysobjects WHERE name = 'HRAddMasterLevel' AND user_name(uid) = 'dbo')
	DROP PROCEDURE [dbo].[HRAddMasterLevel];
GO

CREATE PROCEDURE [dbo].[HRAddMasterLevel]
(
	@LevelName nvarchar(100)
)
AS
	SET NOCOUNT OFF;
	INSERT INTO MasterLevel(LevelName) VALUES (@LevelName);
	SELECT MasterLevelID, LevelName FROM MasterLevel WHERE (MasterLevelID = @@IDENTITY);
GO



IF EXISTS (SELECT * FROM sysobjects WHERE name = 'HRUpdateMasterLevel' AND user_name(uid) = 'dbo')
	DROP PROCEDURE [dbo].[HRUpdateMasterLevel];
GO

CREATE PROCEDURE [dbo].[HRUpdateMasterLevel]
(
	@LevelName nvarchar(100),
	@Original_MasterLevelID int,
	@MasterLevelID int
)
AS
	SET NOCOUNT OFF;
	UPDATE MasterLevel SET LevelName = @LevelName WHERE (MasterLevelID = @Original_MasterLevelID);
	SELECT MasterLevelID, LevelName FROM MasterLevel WHERE (MasterLevelID = @MasterLevelID);
GO



IF EXISTS (SELECT * FROM sysobjects WHERE name = 'HRDeleteMasterLevel' AND user_name(uid) = 'dbo')
	DROP PROCEDURE [dbo].[HRDeleteMasterLevel];
GO

CREATE PROCEDURE [dbo].[HRDeleteMasterLevel]
(
	@Original_MasterLevelID int
)
AS
	SET NOCOUNT OFF;
	UPDATE MasterLevel SET Deleted=1 WHERE (MasterLevelID = @Original_MasterLevelID);
GO


--------------- BILL STORED PROCS -------------------------------------
IF EXISTS (SELECT * FROM sysobjects WHERE name = 'HRGetBills' AND user_name(uid) = 'dbo')
	DROP PROCEDURE [dbo].[HRGetBills];
GO

CREATE PROCEDURE [dbo].[HRGetBills]
	@ClientID as INT
AS
	SET NOCOUNT ON;
	SELECT BillID, PaymentSumm, ClientID, OperatorID, CreationTime 
	FROM Bill
	WHERE ClientID=@ClientID
GO



IF EXISTS (SELECT * FROM sysobjects WHERE name = 'HRAddBill' AND user_name(uid) = 'dbo')
	DROP PROCEDURE [dbo].[HRAddBill];
GO

CREATE PROCEDURE [dbo].[HRAddBill]
(
	@PaymentSumm float,
	@ClientID int,
	@OperatorID int,
	@CreationTime datetime
)
AS
	SET NOCOUNT OFF;
INSERT INTO Bill(PaymentSumm, ClientID, OperatorID, CreationTime) VALUES (@PaymentSumm, @ClientID, @OperatorID, @CreationTime);
	SELECT BillID, PaymentSumm, ClientID, OperatorID, CreationTime FROM Bill WHERE (BillID = @@IDENTITY);
GO



IF EXISTS (SELECT * FROM sysobjects WHERE name = 'HRUpdateBill' AND user_name(uid) = 'dbo')
	DROP PROCEDURE [dbo].[HRUpdateBill];
GO

CREATE PROCEDURE [dbo].[HRUpdateBill]
(
	@PaymentSumm float,
	@ClientID int,
	@OperatorID int,
	@CreationTime datetime,
	@Original_BillID int,
	@BillID int
)
AS
	SET NOCOUNT OFF;
UPDATE Bill SET PaymentSumm = @PaymentSumm, ClientID = @ClientID, OperatorID = @OperatorID, CreationTime = @CreationTime WHERE (BillID = @Original_BillID);
	SELECT BillID, PaymentSumm, ClientID, OperatorID, CreationTime FROM Bill WHERE (BillID = @BillID);
GO



IF EXISTS (SELECT * FROM sysobjects WHERE name = 'HRDeleteBill' AND user_name(uid) = 'dbo')
	DROP PROCEDURE [dbo].[HRDeleteBill];
GO

CREATE PROCEDURE [dbo].[HRDeleteBill]
(
	@Original_BillID int
)
AS
	SET NOCOUNT OFF;
DELETE FROM Bill WHERE (BillID = @Original_BillID);
GO

--------------- SERVICEPARAM STORED PROCS --------------------
IF EXISTS (SELECT * FROM sysobjects WHERE name = 'HRGetServiceParam' AND user_name(uid) = 'dbo')
	DROP PROCEDURE [dbo].[HRGetServiceParam];
GO

CREATE PROCEDURE [dbo].[HRGetServiceParam]
AS
	SET NOCOUNT ON;
SELECT ServiceParamID, ServiceID, MasterLevelID, Price, Duration FROM ServiceParam;
GO



IF EXISTS (SELECT * FROM sysobjects WHERE name = 'HRAddServiceParam' AND user_name(uid) = 'dbo')
	DROP PROCEDURE [dbo].[HRAddServiceParam];
GO

CREATE PROCEDURE [dbo].[HRAddServiceParam]
(
	@ServiceID int,
	@MasterLevelID int,
	@Price money,
	@Duration int
)
AS
	SET NOCOUNT OFF;
INSERT INTO ServiceParam(ServiceID, MasterLevelID, Price, Duration) VALUES (@ServiceID, @MasterLevelID, @Price, @Duration);
	SELECT ServiceParamID, ServiceID, MasterLevelID, Price, Duration FROM ServiceParam WHERE (ServiceParamID = @@IDENTITY);
GO



IF EXISTS (SELECT * FROM sysobjects WHERE name = 'HRUpdateServiceParam' AND user_name(uid) = 'dbo')
	DROP PROCEDURE [dbo].[HRUpdateServiceParam];
GO

CREATE PROCEDURE [dbo].[HRUpdateServiceParam]
(
	@ServiceID int,
	@MasterLevelID int,
	@Price money,
	@Duration int,
	@Original_ServiceParamID int,
	@ServiceParamID int
)
AS
	SET NOCOUNT OFF;
UPDATE ServiceParam SET ServiceID = @ServiceID, MasterLevelID = @MasterLevelID, Price = @Price, Duration = @Duration WHERE (ServiceParamID = @Original_ServiceParamID);
	SELECT ServiceParamID, ServiceID, MasterLevelID, Price, Duration FROM ServiceParam WHERE (ServiceParamID = @ServiceParamID);
GO



IF EXISTS (SELECT * FROM sysobjects WHERE name = 'HRDeleteServiceParam' AND user_name(uid) = 'dbo')
	DROP PROCEDURE [dbo].[HRDeleteServiceParam];
GO

CREATE PROCEDURE [dbo].[HRDeleteServiceParam]
(
	@Original_ServiceParamID int
)
AS
	SET NOCOUNT OFF;
DELETE FROM ServiceParam WHERE (ServiceParamID = @Original_ServiceParamID);
GO

------------- APPOINTMENT STORED PROCS ---------------------------------
IF EXISTS (SELECT * FROM sysobjects WHERE name = 'HRGetAppointments' AND user_name(uid) = 'dbo')
	DROP PROCEDURE [dbo].[HRGetAppointments];
GO

CREATE PROCEDURE [dbo].[HRGetAppointments] 
	@Start datetime = NULL,
	@End  datetime = NULL,
	@Client int = NULL,
	@Performer int = NULL,
	@PendingOnly bit = NULL
AS
	SET NOCOUNT ON;
	SELECT AppointmentID, ClientID, PerformerID, OperatorID, StartTime, FinishTime, CreationTime, ExpectedDuration, Comment, BillID, ServiceID, ServiceCost, Description 
	FROM Appointment 
	WHERE 
		(Deleted = 0)
		AND ((ClientID=@Client) OR (@Client IS NULL))
		AND ((StartTime BETWEEN @Start AND @End) OR (FinishTime BETWEEN @Start AND @End) OR (@Start IS NULL) OR (@End IS NULL))
		AND ((PerformerID=@Performer) OR (@Performer IS NULL))
		AND (@PendingOnly=0 OR ((BillID IS NULL) AND (StartTime>=getdate())))
GO


IF EXISTS (SELECT * FROM sysobjects WHERE name = 'HRAddAppointment' AND user_name(uid) = 'dbo')
	DROP PROCEDURE [dbo].[HRAddAppointment];
GO

CREATE PROCEDURE [dbo].[HRAddAppointment]
(
	@ClientID int,
	@PerformerID int,
	@OperatorID int,
	@StartTime datetime,
	@FinishTime datetime,
	@CreationTime datetime,
	@ExpectedDuration int,
	@Comment nvarchar(512),
	@BillID int,
	@ServiceID int,
	@ServiceCost money,
	@Description nvarchar(512)
)
AS
	SET NOCOUNT OFF;
INSERT INTO Appointment(ClientID, PerformerID, OperatorID, StartTime, FinishTime, CreationTime, ExpectedDuration, Comment, BillID, ServiceID, ServiceCost, Description) VALUES (@ClientID, @PerformerID, @OperatorID, @StartTime, @FinishTime, @CreationTime, @ExpectedDuration, @Comment, @BillID, @ServiceID, @ServiceCost, @Description);
	SELECT AppointmentID, ClientID, PerformerID, OperatorID, StartTime, FinishTime, CreationTime, ExpectedDuration, Comment, BillID, ServiceID, ServiceCost, Description FROM Appointment WHERE (AppointmentID = @@IDENTITY);
GO



IF EXISTS (SELECT * FROM sysobjects WHERE name = 'HRUpdateAppointments' AND user_name(uid) = 'dbo')
	DROP PROCEDURE [dbo].[HRUpdateAppointments];
GO

CREATE PROCEDURE [dbo].[HRUpdateAppointments]
(
	@ClientID int,
	@PerformerID int,
	@OperatorID int,
	@StartTime datetime,
	@FinishTime datetime,
	@CreationTime datetime,
	@ExpectedDuration int,
	@Comment nvarchar(512),
	@BillID int,
	@ServiceID int,
	@ServiceCost money,
	@Description nvarchar(512),
	@Original_AppointmentID int,
	@AppointmentID int
)
AS
	SET NOCOUNT OFF;
UPDATE Appointment SET ClientID = @ClientID, PerformerID = @PerformerID, OperatorID = @OperatorID, StartTime = @StartTime, FinishTime = @FinishTime, CreationTime = @CreationTime, ExpectedDuration = @ExpectedDuration, Comment = @Comment, BillID = @BillID, ServiceID = @ServiceID, ServiceCost = @ServiceCost, Description = @Description WHERE (AppointmentID = @Original_AppointmentID);
	SELECT AppointmentID, ClientID, PerformerID, OperatorID, StartTime, FinishTime, CreationTime, ExpectedDuration, Comment, BillID, ServiceID, ServiceCost, Description FROM Appointment WHERE (AppointmentID = @AppointmentID);
GO



IF EXISTS (SELECT * FROM sysobjects WHERE name = 'HRDeleteAppointments' AND user_name(uid) = 'dbo')
	DROP PROCEDURE [dbo].[HRDeleteAppointments];
GO

CREATE PROCEDURE [dbo].[HRDeleteAppointments]
(
	@Original_AppointmentID int
)
AS
	SET NOCOUNT OFF;
	UPDATE Appointment SET Deleted=1 WHERE (AppointmentID = @Original_AppointmentID);
GO


-------------- PAYMETNT STORED PROCS ----------------------
IF EXISTS (SELECT * FROM sysobjects WHERE name = 'HRGetPayments' AND user_name(uid) = 'dbo')
	DROP PROCEDURE [dbo].[HRGetPayments];
GO

CREATE PROCEDURE [dbo].[HRGetPayments]
AS
	SET NOCOUNT ON;
SELECT PaymentID, PaymentType, CardName, PaymentSumm, BillID FROM Payment;
GO



IF EXISTS (SELECT * FROM sysobjects WHERE name = 'HRAddPayment' AND user_name(uid) = 'dbo')
	DROP PROCEDURE [dbo].[HRAddPayment];
GO

CREATE PROCEDURE [dbo].[HRAddPayment]
(
	@PaymentType tinyint,
	@CardName nvarchar(128),
	@PaymentSumm float,
	@BillID int
)
AS
	SET NOCOUNT OFF;
INSERT INTO Payment(PaymentType, CardName, PaymentSumm, BillID) VALUES (@PaymentType, @CardName, @PaymentSumm, @BillID);
	SELECT PaymentID, PaymentType, CardName, PaymentSumm, BillID FROM Payment WHERE (PaymentID = @@IDENTITY);
GO



IF EXISTS (SELECT * FROM sysobjects WHERE name = 'HRUpdatePayment' AND user_name(uid) = 'dbo')
	DROP PROCEDURE [dbo].[HRUpdatePayment];
GO

CREATE PROCEDURE [dbo].[HRUpdatePayment]
(
	@PaymentType tinyint,
	@CardName nvarchar(128),
	@PaymentSumm float,
	@BillID int,
	@Original_PaymentID int,
	@PaymentID int
)
AS
	SET NOCOUNT OFF;
UPDATE Payment SET PaymentType = @PaymentType, CardName = @CardName, PaymentSumm = @PaymentSumm, BillID = @BillID WHERE (PaymentID = @Original_PaymentID);
	SELECT PaymentID, PaymentType, CardName, PaymentSumm, BillID FROM Payment WHERE (PaymentID = @PaymentID);
GO



IF EXISTS (SELECT * FROM sysobjects WHERE name = 'HRDeletePayment' AND user_name(uid) = 'dbo')
	DROP PROCEDURE [dbo].[HRDeletePayment];
GO

CREATE PROCEDURE [dbo].[HRDeletePayment]
(
	@Original_PaymentID int
)
AS
	SET NOCOUNT OFF;
DELETE FROM Payment WHERE (PaymentID = @Original_PaymentID);
GO

----------------- BILLPRODUCT STORED PROCS ---------------------------------
IF EXISTS (SELECT * FROM sysobjects WHERE name = 'HRGetBillProducts' AND user_name(uid) = 'dbo')
	DROP PROCEDURE [dbo].[HRGetBillProducts];
GO

CREATE PROCEDURE [dbo].[HRGetBillProducts]
AS
	SET NOCOUNT ON;
SELECT BillProductID, ProductID, BillID, Cost FROM BillProduct;
GO



IF EXISTS (SELECT * FROM sysobjects WHERE name = 'HRAddBillProduct' AND user_name(uid) = 'dbo')
	DROP PROCEDURE [dbo].[HRAddBillProduct];
GO

CREATE PROCEDURE [dbo].[HRAddBillProduct]
(
	@ProductID int,
	@BillID int,
	@Cost float
)
AS
	SET NOCOUNT OFF;
INSERT INTO BillProduct(ProductID, BillID, Cost) VALUES (@ProductID, @BillID, @Cost);
	SELECT BillProductID, ProductID, BillID, Cost FROM BillProduct WHERE (BillProductID = @@IDENTITY);
GO



IF EXISTS (SELECT * FROM sysobjects WHERE name = 'HRUpdateBillProduct' AND user_name(uid) = 'dbo')
	DROP PROCEDURE [dbo].[HRUpdateBillProduct];
GO

CREATE PROCEDURE [dbo].[HRUpdateBillProduct]
(
	@ProductID int,
	@BillID int,
	@Cost float,
	@Original_BillProductID int,
	@BillProductID int
)
AS
	SET NOCOUNT OFF;
UPDATE BillProduct SET ProductID = @ProductID, BillID = @BillID, Cost = @Cost WHERE (BillProductID = @Original_BillProductID);
	SELECT BillProductID, ProductID, BillID, Cost FROM BillProduct WHERE (BillProductID = @BillProductID);
GO



IF EXISTS (SELECT * FROM sysobjects WHERE name = 'HRDeleteBillProduct' AND user_name(uid) = 'dbo')
	DROP PROCEDURE [dbo].[HRDeleteBillProduct];
GO

CREATE PROCEDURE [dbo].[HRDeleteBillProduct]
(
	@Original_BillProductID int
)
AS
	SET NOCOUNT OFF;
DELETE FROM BillProduct WHERE (BillProductID = @Original_BillProductID);
GO


--- BIILNOSALE STORED PROCS ----------------------------
IF EXISTS (SELECT * FROM sysobjects WHERE name = 'HRGetPettyCashBills' AND user_name(uid) = 'dbo')
	DROP PROCEDURE [dbo].[HRGetPettyCashBills];
GO

CREATE PROCEDURE [dbo].[HRGetPettyCashBills]
AS
	SET NOCOUNT ON;
SELECT BillNoSaleID, PaymentSum, Comment, EmployeeID, CreationTime, OperatorID FROM BillNoSale;
GO



IF EXISTS (SELECT * FROM sysobjects WHERE name = 'HRAddPettyCashBills' AND user_name(uid) = 'dbo')
	DROP PROCEDURE [dbo].[HRAddPettyCashBills];
GO

CREATE PROCEDURE [dbo].[HRAddPettyCashBills]
(
	@PaymentSum float,
	@Comment nvarchar(512),
	@EmployeeID int,
	@CreationTime datetime,
	@OperatorID int
)
AS
	SET NOCOUNT OFF;
INSERT INTO BillNoSale(PaymentSum, Comment, EmployeeID, CreationTime, OperatorID) VALUES (@PaymentSum, @Comment, @EmployeeID, @CreationTime, @OperatorID);
	SELECT BillNoSaleID, PaymentSum, Comment, EmployeeID, CreationTime, OperatorID FROM BillNoSale WHERE (BillNoSaleID = @@IDENTITY);
GO



IF EXISTS (SELECT * FROM sysobjects WHERE name = 'HRUpdatePettyCashBills' AND user_name(uid) = 'dbo')
	DROP PROCEDURE [dbo].[HRUpdatePettyCashBills];
GO

CREATE PROCEDURE [dbo].[HRUpdatePettyCashBills]
(
	@PaymentSum float,
	@Comment nvarchar(512),
	@EmployeeID int,
	@CreationTime datetime,
	@OperatorID int,
	@Original_BillNoSaleID int,
	@BillNoSaleID int
)
AS
	SET NOCOUNT OFF;
UPDATE BillNoSale SET PaymentSum = @PaymentSum, Comment = @Comment, EmployeeID = @EmployeeID, CreationTime = @CreationTime, OperatorID = @OperatorID WHERE (BillNoSaleID = @Original_BillNoSaleID);
	SELECT BillNoSaleID, PaymentSum, Comment, EmployeeID, CreationTime, OperatorID FROM BillNoSale WHERE (BillNoSaleID = @BillNoSaleID);
GO



IF EXISTS (SELECT * FROM sysobjects WHERE name = 'HRDeletePettyCashBills' AND user_name(uid) = 'dbo')
	DROP PROCEDURE [dbo].[HRDeletePettyCashBills];
GO

CREATE PROCEDURE [dbo].[HRDeletePettyCashBills]
(
	@Original_BillNoSaleID int
)
AS
	SET NOCOUNT OFF;
DELETE FROM BillNoSale WHERE (BillNoSaleID = @Original_BillNoSaleID);
GO


------ REPORT FUNCTIONS --------------------------
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[HRFirstAppointment]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[HRFirstAppointment]
GO


CREATE FUNCTION dbo.HRFirstAppointment
	(
		@ClientID int
	)
RETURNS int
AS
BEGIN
	RETURN (SELECT TOP 1 AppointmentID FROM Appointment WHERE ClientID=@ClientID ORDER BY StartTime)
END


GO
----- END OF DAY REPORT -------------------

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[HRAuditDayReport]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[HRAuditDayReport]
GO


CREATE PROCEDURE dbo.HRAuditDayReport
	@StartDate as datetime,
	@EndDate as datetime
AS
	SET NOCOUNT ON;
	DECLARE @Date nvarchar(50)
	DECLARE @Time nvarchar(50)
	DECLARE @GiftCertificate money
	DECLARE @Cash money
	DECLARE @Cheque money
	DECLARE @Electron money
	DECLARE @Visa money
	DECLARE @Delta money
	DECLARE @Other money
	DECLARE @Name nvarchar(128)
	DECLARE @BillID int


	CREATE TABLE #Payment
	(
		[Date] nvarchar(50),
		[Time] nvarchar(50),
		[CustomerName] nvarchar(128),
		[Cash] money,
		[GiftCertificate] money,
		[Cheque] money,
		[Electron] money,
		[Visa] money,
		[Delta] money,
		[Other] money,
		[Total] money
	)

	DECLARE PaymentCursor CURSOR LOCAL READ_ONLY FORWARD_ONLY FOR 
	SELECT 
		CONVERT (varchar(50), b.CreationTime, 103) AS Date, 
		CONVERT (varchar(50), b.CreationTime, 108) AS Time, 
		IsNull(c.FullName, 'Product Sale') AS 'CustomerName',
		b.BillID
	FROM Bill b 
	LEFT OUTER JOIN Client c ON b.ClientID = c.ClientID
	WHERE b.CreationTime between @StartDate and @EndDate

	OPEN PaymentCursor

	FETCH NEXT FROM PaymentCursor INTO @Date, @Time, @Name, @BillID

	WHILE @@FETCH_STATUS = 0
	BEGIN
		SELECT @Cash=IsNull(SUM(p.PaymentSumm),0)	FROM Payment p	INNER JOIN Bill b ON b.BillId=p.BillId	WHERE b.BillID=@BillID 
			AND p.PaymentType=0
		SELECT @Cheque=IsNull(SUM(p.PaymentSumm), 0)	FROM Payment p	INNER JOIN Bill b ON b.BillId=p.BillId	WHERE b.BillID=@BillID 
			AND p.PaymentType=1
		SELECT @Electron=IsNull(SUM(p.PaymentSumm), 0)	FROM Payment p	INNER JOIN Bill b ON b.BillId=p.BillId	WHERE b.BillID=@BillID 
			AND p.PaymentType=2 and p.CardName='Electron'
		SELECT @Visa=IsNull(SUM(p.PaymentSumm), 0)	FROM Payment p	INNER JOIN Bill b ON b.BillId=p.BillId	WHERE b.BillID=@BillID 
			AND p.PaymentType=2 and p.CardName='Visa'
		SELECT @Delta=IsNull(SUM(p.PaymentSumm), 0)	FROM Payment p	INNER JOIN Bill b ON b.BillId=p.BillId	WHERE b.BillID=@BillID 
			AND p.PaymentType=2 and p.CardName='Delta'
		SELECT @GiftCertificate=IsNull(SUM(p.PaymentSumm), 0)	FROM Payment p	INNER JOIN Bill b ON b.BillId=p.BillId	WHERE b.BillID=@BillID 
			AND p.PaymentType=2 and p.CardName='Gift Certificate'
		SELECT @Other=IsNull(SUM(p.PaymentSumm), 0)	FROM Payment p	INNER JOIN Bill b ON b.BillId=p.BillId	WHERE b.BillID=@BillID 
			AND p.PaymentType=2 and p.CardName not in ('Gift Certificate', 'Delta', 'Visa', 'Electron')


		INSERT INTO #Payment values (@Date, @Time, @Name,
			@Cash, @GiftCertificate, @Cheque, @Electron, @Visa, @Delta, @Other, 
			@Cash + @GiftCertificate + @Cheque + @Electron + @Visa + @Delta + @Other)

		FETCH NEXT FROM PaymentCursor INTO @Date, @Time, @Name, @BillID
	END


	CLOSE PaymentCursor
	DEALLOCATE PaymentCursor

	-- Add Petty Cash info
	INSERT INTO #Payment SELECT 
			CONVERT (varchar(50), b.CreationTime, 103),
			CONVERT (varchar(50), b.CreationTime, 108),
			'No Sale (' + Left(b.Comment, 50) + ')', 
			-b.PaymentSum, 0, 0, 0, 0, 0, 0, -b.PaymentSum
		FROM BillNoSale b WHERE b.CreationTime between @StartDate and @EndDate

	SELECT * FROM #Payment
	DROP TABLE #Payment
GO


if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[HRPropertiesDayReport]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[HRPropertiesDayReport]
GO

CREATE PROCEDURE dbo.HRPropertiesDayReport
	@StartDate as datetime,
	@EndDate as datetime
AS

	SET NOCOUNT ON;
	
DECLARE @CertReceived money
DECLARE @CertReceivedNumber int
DECLARE @RetailSold money
DECLARE @RetailNumber int
DECLARE @ServicesSold money
DECLARE @ServicesNumber int
DECLARE @PettySold money
DECLARE @PettyNumber int


SELECT @CertReceived=IsNull(sum(p.PaymentSumm),0), @CertReceivedNumber=IsNull(count(*),0) FROM Payment p INNER JOIN Bill b ON b.BillID=p.BillID 
WHERE (b.CreationTime between @StartDate and @EndDate)
AND PaymentType=2 And CardName='Gold Certificate'

SELECT @RetailSold=IsNull(sum(p.Cost),0), @RetailNumber=IsNull(count(*),0) FROM BillProduct p INNER JOIN Bill b ON b.BillID=p.BillID 
WHERE (b.CreationTime between @StartDate and @EndDate)

SELECT @ServicesSold=IsNull(sum(p.ServiceCost),0), @ServicesNumber=IsNull(count(*),0) FROM Appointment p INNER JOIN Bill b ON b.BillID=p.BillID 
WHERE (b.CreationTime between @StartDate and @EndDate) 

SELECT @PettySold=-IsNull(sum(PaymentSum),0), @PettyNumber=IsNull(count(*),0) FROM BillNoSale b
WHERE (b.CreationTime between @StartDate and @EndDate) 


SELECT 
	Round(@CertReceived,2) as 'CertReceived', 
	@CertReceivedNumber as 'CertReceivedNumber',
	0.00 as 'CertSold',
	0 as 'CertSoldNumber',
	0 as 'OTC',
	0 as 'OTCNumber',
	Round(@PettySold,2)  as 'PettyCash',
	@PettyNumber as 'PettyCashNumber',
	@RetailNumber as 'RetailNumber',
	Round(@RetailSold,2) as 'RetailSold',
	@ServicesNumber as 'ServicesNumber',
	Round(@ServicesSold,2) as 'ServicesSold'


	
	
GO


if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[HRPaymentDayReport]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[HRPaymentDayReport]
GO


CREATE PROCEDURE dbo.HRPaymentDayReport
	@StartDate as datetime,
	@EndDate as datetime
AS
	SET NOCOUNT ON;
	SELECT 
		case PaymentType 
			when 0 then 'Cash' 
			when 1 then 'Cheque'
			else CardName
		end as 'PaymentType',
		sum(p.PaymentSumm) as 'Amount',
		count(p.PaymentSumm) as 'Number'
	FROM Bill b 
	INNER JOIN PaymentView p On p.BillID=b.BillID
	WHERE b.CreationTime between @StartDate and @EndDate
	GROUP By PaymentType, CardName
GO



------ FULL SALON REPORT STORED PROCS ------------------

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[HRClientTypeForEmployee]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[HRClientTypeForEmployee]
GO
/*
CREATE FUNCTION HRClientTypeForEmployee(@ClientID int, @EmployeeID int, @Time datetime)
RETURNS int
AS
BEGIN

DECLARE @Most int
DECLARE @First int
DECLARE @Result int

SELECT TOP 1 @Most=PerformerID FROM Appointment 
WHERE StartTime<@Time AND ClientID=@ClientID AND Deleted=0 
GROUP BY PerformerID
ORDER BY Count(*) DESC, PerformerID DESC

IF @Most IS NULL 
	BEGIN
		SELECT TOP 1 @First=PerformerID FROM Appointment 
		WHERE ClientID=@CLientID
		ORDER BY StartTime ASC

		IF @First=@EmployeeID 
			SET @Result=0
		ELSE
			SET @Result=1
	END
ELSE
	BEGIN
		IF @Most=@EmployeeID 
			SET @Result=1
		ELSE
			SET @Result=2
	END

	RETURN @Result

END
*/
GO


if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[PaidItems]') and OBJECTPROPERTY(id, N'IsView') = 1)
drop view [dbo].[PaidItems]
GO

/*
CREATE  VIEW PaidItems AS

SELECT a.ClientID, a.PerformerID as EmployeeID, a.ServiceCost as ItemCost, b.CreationTime, 'SERVICE' as ItemType
FROM Appointment a
INNER JOIN Bill b ON b.BillID=a.BillID

UNION ALL


SELECT b.ClientID,  b.OperatorID, Cost, b.CreationTime, 'PRODUCT'
FROM Bill b
INNER JOIN BillProduct bp ON bp.BillID=b.BillID
*/

GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[HRGetPaidItems]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[HRGetPaidItems]
GO
/*
CREATE FUNCTION HRGetPaidItems(@Start datetime, @End datetime)
RETURNS TABLE
AS
RETURN SELECT ClientID, EmployeeID, Sum(ItemCost) as ItemsCost FROM PaidItems 
	WHERE CreationTime BETWEEN @Start AND @End GROUP BY ClientID, EmployeeID

*/
GO





if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[HREmployeeFullReport]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[HREmployeeFullReport]
GO

/*
CREATE PROCEDURE dbo.HREmployeeFullReport
	@StartDate as datetime,
	@EndDate as datetime
AS
SET NOCOUNT ON

DECLARE @Name nvarchar(128)
DECLARE @EID int
DECLARE @NewCol int
DECLARE @NewMoney money
DECLARE @RegCol int
DECLARE @RegMoney money
DECLARE @TranCol int
DECLARE @TranMoney money
DECLARE @Col int
DECLARE @Active bit


DECLARE EmployeeCursor CURSOR LOCAL READ_ONLY FORWARD_ONLY FOR 
SELECT e.EmployeeID, e.FullName, e.IsAttendant FROM Employee e WHERE Deleted=0 AND Sys=0

CREATE TABLE #Result
(
	[Name] nvarchar(128),
	[ClientData] nvarchar(128),
	[ColType] nvarchar(128),
	[ColValue] money
)




OPEN EmployeeCursor

FETCH NEXT FROM EmployeeCursor INTO @EID, @Name, @Active

WHILE @@FETCH_STATUS = 0
BEGIN
	SELECT @NewCol=IsNull(Count(*),0), @NewMoney=IsNull(Sum(ItemsCost),0) FROM dbo.HRGetPaidItems(@StartDate, @EndDate)
	WHERE EmployeeID=@EID 
	AND dbo.HRClientTypeForEmployee(ClientID, EmployeeID, @StartDate)=0

	SELECT @RegCol=IsNull(Count(*),0), @RegMoney=IsNull(Sum(ItemsCost),0) FROM dbo.HRGetPaidItems(@StartDate, @EndDate)
	WHERE EmployeeID=@EID 
	AND dbo.HRClientTypeForEmployee(ClientID, EmployeeID, @StartDate)=1

	SELECT @TranCol=IsNull(Count(*),0), @TranMoney=IsNull(Sum(ItemsCost),0) FROM dbo.HRGetPaidItems(@StartDate, @EndDate)
	WHERE EmployeeID=@EID 
	AND dbo.HRClientTypeForEmployee(ClientID, EmployeeID, @StartDate)=2

	SET @Col = @TranCol  + @RegCol + @NewCol
	
	IF (@Col <> 0) or (@Active = 1)
	BEGIN 
		INSERT INTO #Result VALUES (@Name, N'New Customer', N'£', @NewMoney)
		INSERT INTO #Result VALUES (@Name, N'New Customer', N'No Off', @NewCol)
		INSERT INTO #Result VALUES (@Name, N'New Customer', N'%', CASE @Col WHEN 0 THEN 0 ELSE Round(100*@NewCol/@Col,2) END)

		INSERT INTO #Result VALUES (@Name, N'Repeat Customers', N'£', @RegMoney)
		INSERT INTO #Result VALUES (@Name, N'Repeat Customers', N'No Off', @RegCol)
		INSERT INTO #Result VALUES (@Name, N'Repeat Customers', N'%', CASE @Col WHEN 0 THEN 0 ELSE Round(100*@RegCol/@Col,2) END)

		INSERT INTO #Result VALUES (@Name, N'Transfer Customers', N'£', @TranMoney)
		INSERT INTO #Result VALUES (@Name, N'Transfer Customers', N'No Off',@TranCol)
		INSERT INTO #Result VALUES (@Name, N'Transfer Customers', N'%', CASE @Col WHEN 0 THEN 0 ELSE Round(100*@TranCol/@Col,2) END)
	END
	
	FETCH NEXT FROM EmployeeCursor INTO @EID, @Name, @Active
END

SELECT * FROM #Result

DROP TABLE #Result

RETURN 
*/
GO



if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[HRProductFullReport]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[HRProductFullReport]
GO


CREATE PROCEDURE dbo.HRProductFullReport
	@StartDate as datetime,
	@EndDate as datetime
AS

SET NOCOUNT ON

DECLARE @Name nvarchar(128)
DECLARE @EID int
DECLARE @Amount int
DECLARE @Active bit

DECLARE EmployeeCursor CURSOR LOCAL READ_ONLY FORWARD_ONLY FOR 
SELECT e.EmployeeID, e.FullName, e.IsAttendant  FROM Employee e WHERE Deleted=0 AND Sys=0

CREATE TABLE #Result
(
	[Name] nvarchar(128),
	[ProductCategory] nvarchar(128),
	[ColType] nvarchar(128),
	[ColValue] float
)




OPEN EmployeeCursor

FETCH NEXT FROM EmployeeCursor INTO @EID, @Name, @Active

WHILE @@FETCH_STATUS = 0
BEGIN
	SELECT @Amount=COUNT(*) FROM BillProduct bp
	INNER JOIN Bill p ON bp.BillID=p.BillID
	WHERE OperatorID=@EID
	AND p.CreationTime between @StartDate and @EndDate


	DECLARE @Category nvarchar(128)
	DECLARE @Count int
	DECLARE @Money money
	DECLARE ProductCursor CURSOR LOCAL READ_ONLY FORWARD_ONLY FOR
	SELECT p.Type, IsNull(SUM(bp.Cost),0), COUNT(bp.Cost) FROM BillProduct bp
		INNER JOIN Bill b ON bp.BillID=b.BillID AND b.OperatorID=@EID
		RIGHT OUTER JOIN Product p ON p.ProductID=bp.ProductID
		WHERE b.CreationTime between @StartDate and @EndDate
		GROUP BY p.Type
	OPEN ProductCursor
	
	FETCH NEXT FROM ProductCursor INTO @Category, @Money, @Count

	WHILE @@FETCH_STATUS = 0
	BEGIN
		IF (@Count<>0 ) OR (@Active=1)
		BEGIN
			INSERT INTO #Result VALUES (@Name, @Category, N'£', @Money)
			INSERT INTO #Result VALUES (@Name, @Category, N'No Off', @Count)
			--INSERT INTO #Result VALUES (@Name, @Category, N'%', CASE @Amount WHEN 0 THEN 0 ELSE 100*cast(@Count as money)/cast(@Amount as money) END)
		END

		FETCH NEXT FROM ProductCursor INTO @Category, @Money, @Count
	END

	CLOSE ProductCursor
	DEALLOCATE ProductCursor

	FETCH NEXT FROM EmployeeCursor INTO @EID, @Name, @Active
END

SELECT * FROM #Result

DROP TABLE #Result

RETURN 

GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[HRServiceFullReport]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[HRServiceFullReport]
GO

CREATE PROCEDURE dbo.HRServiceFullReport
	@StartDate as datetime,
	@EndDate as datetime
AS

SET NOCOUNT ON

DECLARE @Name nvarchar(128)
DECLARE @EID int
DECLARE @Amount int
DECLARE @Active bit

DECLARE EmployeeCursor CURSOR LOCAL READ_ONLY FORWARD_ONLY FOR 
SELECT e.EmployeeID, e.FullName, e.IsAttendant  FROM Employee e WHERE Deleted=0 AND Sys=0

CREATE TABLE #Result
(
	[Name] nvarchar(128),
	[Service] nvarchar(128),
	[ColType] nvarchar(128),
	[ColValue] float
)




OPEN EmployeeCursor

FETCH NEXT FROM EmployeeCursor INTO @EID, @Name, @Active

WHILE @@FETCH_STATUS = 0
BEGIN
	SELECT @Amount=COUNT(*) FROM Appointment
	WHERE PerformerID=@EID AND NOT BillID IS NULL


	DECLARE @Title nvarchar(128)
	DECLARE @Count int
	DECLARE @Money money
	DECLARE ServiceCursor CURSOR LOCAL READ_ONLY FORWARD_ONLY FOR
	SELECT s.Type, IsNull(SUM(a.ServiceCost),0), COUNT(a.ServiceCost) 
		FROM Service s
		LEFT OUTER JOIN Appointment a ON a.ServiceID=s.ServiceID AND not a.BillID IS NULL AND a.PerformerID=@EID
		WHERE a.StartTime between @StartDate and @EndDate
		GROUP BY s.Type
	OPEN ServiceCursor
	
	FETCH NEXT FROM ServiceCursor INTO @Title, @Money, @Count

	WHILE @@FETCH_STATUS = 0
	BEGIN
		IF (@Count<>0 ) OR (@Active = 1)
		BEGIN
			INSERT INTO #Result VALUES (@Name, @Title, N'£', @Money)
			INSERT INTO #Result VALUES (@Name, @Title, N'No Off', @Count)
			--INSERT INTO #Result VALUES (@Name, @Title, N'%', CASE @Amount WHEN 0 THEN 0 ELSE ROUND(100*cast(@Count as float)/cast(@Amount as float),2) END)
		END

		FETCH NEXT FROM ServiceCursor INTO @Title, @Money, @Count
	END

	CLOSE ServiceCursor
	DEALLOCATE ServiceCursor

	FETCH NEXT FROM EmployeeCursor INTO @EID, @Name, @Active
END

SELECT * FROM #Result

DROP TABLE #Result

RETURN 
GO
-------------------------- QUICK REPORT STORED PROCS ----------------------
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[HREmployeeQuickReport]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[HREmployeeQuickReport]
GO


CREATE PROCEDURE dbo.HREmployeeQuickReport
	@StartDate as datetime,
	@EndDate as datetime
AS

SET NOCOUNT ON

DECLARE @EID int
DECLARE @Name nvarchar(128)
DECLARE @New int
DECLARE @Total int
DECLARE @Services money
DECLARE @Products money
DECLARE @ServicesAverage money
DECLARE @ProductsAverage money
DECLARE @TotalSum money
DECLARE @Active bit

DECLARE EmployeeCursor CURSOR LOCAL READ_ONLY FORWARD_ONLY FOR 
SELECT e.EmployeeID, e.FullName, e.IsAttendant FROM Employee e WHERE Deleted=0 AND Sys=0

CREATE TABLE #Result
(
	[Name] nvarchar(128),
	[Active] bit,
	[RegularClients] int,
	[NewClients] int,
	[TotalClients] int,
	[Services] money,
	[ServicesAverage] money,
	[Products] money,
	[ProductsAverage] money,
	[TotalSum] money
)




OPEN EmployeeCursor

FETCH NEXT FROM EmployeeCursor INTO @EID, @Name, @Active

WHILE @@FETCH_STATUS = 0
BEGIN
	SELECT @New=COUNT(*) FROM Client 
	WHERE
		ClientID in
		(SELECT DISTINCT ClientID FROM Appointment a WHERE 
			dbo.HRFirstAppointment(ClientID) = AppointmentID
			AND (a.StartTime between @StartDate and @EndDate)
			AND a.PerformerID=@EID 
			AND NOT (a.BillID IS NULL)
		 GROUP BY ClientID)

	SELECT @Total=COUNT(*) FROM Client 
	WHERE
		ClientID in
		(SELECT DISTINCT ClientID FROM Appointment a WHERE 
			(a.StartTime between @StartDate and @EndDate)
			AND a.PerformerID=@EID 
			AND NOT (a.BillID IS NULL)
		 GROUP BY ClientID)

	SELECT @Services=IsNull(SUM(ServiceCost) ,0)
		FROM Appointment a
	WHERE
		(a.StartTime between @StartDate and @EndDate)
		AND a.PerformerID=@EID 
		AND NOT (a.BillID IS NULL)

	SELECT @Products=IsNull(SUM(bp.Cost) ,0)
		FROM Bill b
		INNER JOIN BillProduct bp ON bp.BillID=b.BillID
	WHERE
		(b.CreationTime between @StartDate and @EndDate)
		AND b.OperatorID=@EID 
		
	SET @ServicesAverage = CASE @Total WHEN 0 THEN 0 ELSE ROUND(@Services/@Total, 2) END
	SET @ProductsAverage = CASE @Total WHEN 0 THEN 0 ELSE ROUND(@Products/@Total, 2) END
	SET @TotalSum = @Services + @Products

	INSERT INTO #Result VALUES (@Name, @Active, @Total - @New, @New, @Total, @Services, @ServicesAverage, @Products, @ProductsAverage, @TotalSum)
	FETCH NEXT FROM EmployeeCursor INTO @EID, @Name, @Active
END

SELECT * FROM #Result WHERE ([TotalSum]>0) OR ([Active]=1) ORDER BY [Name]

DROP TABLE #Result

RETURN 

GO

---------------- SCHEDULE STORED PROCS ------------------
IF EXISTS (SELECT * FROM sysobjects WHERE name = 'HRGetSchedule' AND user_name(uid) = 'dbo')
	DROP PROCEDURE [dbo].[HRGetSchedule];
GO

CREATE PROCEDURE [dbo].[HRGetSchedule]
	@StartDate as datetime = NULL,
	@EndDate as datetime = NULL
AS
	SET NOCOUNT ON;
	
	IF @EndDate IS NULL
	BEGIN
		SELECT s.ScheduleID, StartTime, FinishTime, Busy, Period, DayOfWeek, DayOfMonth, ExactDate, Descript, es.EmployeeID 
		FROM Schedule s INNER JOIN EmployeeSchedule es ON es.ScheduleID=s.ScheduleID
		WHERE (ExactDate=@StartDate)
	END 
	ELSE
	BEGIN
		SELECT s.ScheduleID, StartTime, FinishTime, Busy, Period, DayOfWeek, DayOfMonth, ExactDate, Descript, es.EmployeeID 
		FROM Schedule s INNER JOIN EmployeeSchedule es ON es.ScheduleID=s.ScheduleID
		WHERE (ExactDate BETWEEN @StartDate AND @EndDate) OR (ExactDate IS NULL)
	END
	
	--SELECT ScheduleID, StartTime, FinishTime, Busy, Period, DayOfWeek, DayOfMonth, ExactDate, Descript, EmployeeID FROM Schedule;
GO



IF EXISTS (SELECT * FROM sysobjects WHERE name = 'HRAddSchedule' AND user_name(uid) = 'dbo')
	DROP PROCEDURE [dbo].[HRAddSchedule];
GO

CREATE PROCEDURE [dbo].[HRAddSchedule]
(
	@StartTime int,
	@FinishTime int,
	@Busy bit,
	@Period tinyint,
	@DayOfWeek tinyint,
	@DayOfMonth int,
	@ExactDate datetime,
	@Descript nvarchar(512),
	@EmployeeID int
)
AS
	SET NOCOUNT OFF;
INSERT INTO Schedule(StartTime, FinishTime, Busy, Period, DayOfWeek, DayOfMonth, ExactDate, Descript, EmployeeID) VALUES (@StartTime, @FinishTime, @Busy, @Period, @DayOfWeek, @DayOfMonth, @ExactDate, @Descript, @EmployeeID);
	SELECT ScheduleID, StartTime, FinishTime, Busy, Period, DayOfWeek, DayOfMonth, ExactDate, Descript, EmployeeID FROM Schedule WHERE (ScheduleID = @@IDENTITY);
GO



IF EXISTS (SELECT * FROM sysobjects WHERE name = 'HRUpdateSchedule' AND user_name(uid) = 'dbo')
	DROP PROCEDURE [dbo].[HRUpdateSchedule];
GO

CREATE PROCEDURE [dbo].[HRUpdateSchedule]
(
	@StartTime int,
	@FinishTime int,
	@Busy bit,
	@Period tinyint,
	@DayOfWeek tinyint,
	@DayOfMonth int,
	@ExactDate datetime,
	@Descript nvarchar(512),
	@EmployeeID int,
	@Original_ScheduleID int,
	@ScheduleID int
)
AS
	SET NOCOUNT OFF;
UPDATE Schedule SET StartTime = @StartTime, FinishTime = @FinishTime, Busy = @Busy, Period = @Period, DayOfWeek = @DayOfWeek, DayOfMonth = @DayOfMonth, ExactDate = @ExactDate, Descript = @Descript, EmployeeID = @EmployeeID WHERE (ScheduleID = @Original_ScheduleID);
	SELECT ScheduleID, StartTime, FinishTime, Busy, Period, DayOfWeek, DayOfMonth, ExactDate, Descript, EmployeeID FROM Schedule WHERE (ScheduleID = @ScheduleID);
GO



IF EXISTS (SELECT * FROM sysobjects WHERE name = 'HRDeleteSchedule' AND user_name(uid) = 'dbo')
	DROP PROCEDURE [dbo].[HRDeleteSchedule];
GO

CREATE PROCEDURE [dbo].[HRDeleteSchedule]
(
	@Original_ScheduleID int
)
AS
	SET NOCOUNT OFF;
DELETE FROM Schedule WHERE (ScheduleID = @Original_ScheduleID);
GO


















----------------------------- SalonTimes stored procs --------------------------------------
IF EXISTS (SELECT * FROM sysobjects WHERE name = 'HRGetSalonTimes' AND user_name(uid) = 'dbo')
	DROP PROCEDURE [dbo].[HRGetSalonTimes];
GO

CREATE PROCEDURE [dbo].[HRGetSalonTimes]
AS
	SET NOCOUNT ON;
	SELECT ScheduleID, StartTime, FinishTime, DayOfWeek FROM Schedule WHERE (EmployeeID IS NULL) AND (NOT (DayOfWeek IS NULL));
GO



IF EXISTS (SELECT * FROM sysobjects WHERE name = 'HRAddSalonTime' AND user_name(uid) = 'dbo')
	DROP PROCEDURE [dbo].[HRAddSalonTime];
GO

CREATE PROCEDURE [dbo].[HRAddSalonTime]
(
	@StartTime int,
	@FinishTime int,
	@DayOfWeek tinyint
)
AS
	SET NOCOUNT OFF;
	INSERT INTO Schedule(StartTime, FinishTime, DayOfWeek) VALUES (@StartTime, @FinishTime, @DayOfWeek);
	SELECT ScheduleID, StartTime, FinishTime, DayOfWeek FROM Schedule WHERE (ScheduleID = @@IDENTITY);
GO



IF EXISTS (SELECT * FROM sysobjects WHERE name = 'HRUpdateSalonTime' AND user_name(uid) = 'dbo')
	DROP PROCEDURE [dbo].[HRUpdateSalonTime];
GO

CREATE PROCEDURE [dbo].[HRUpdateSalonTime]
(
	@StartTime int,
	@FinishTime int,
	@DayOfWeek tinyint,
	@Original_ScheduleID int,
	@ScheduleID int
)
AS
	SET NOCOUNT OFF;
	UPDATE Schedule SET StartTime = @StartTime, FinishTime = @FinishTime, DayOfWeek = @DayOfWeek WHERE (ScheduleID = @Original_ScheduleID);
	SELECT ScheduleID, StartTime, FinishTime, DayOfWeek FROM Schedule WHERE (ScheduleID = @ScheduleID);
GO



IF EXISTS (SELECT * FROM sysobjects WHERE name = 'HRDeleteSalonTime' AND user_name(uid) = 'dbo')
	DROP PROCEDURE [dbo].[HRDeleteSalonTime];
GO

CREATE PROCEDURE [dbo].[HRDeleteSalonTime]
(
	@Original_ScheduleID int
)
AS
	SET NOCOUNT OFF;
	DELETE FROM Schedule WHERE (ScheduleID = @Original_ScheduleID);
GO
















------------------ EmployeeTimes STORED PROCS -----------------------------------------
IF EXISTS (SELECT * FROM sysobjects WHERE name = 'HRGetEmployeeTimes' AND user_name(uid) = 'dbo')
	DROP PROCEDURE [dbo].[HRGetEmployeeTimes];
GO

CREATE PROCEDURE [dbo].[HRGetEmployeeTimes]
AS
	SET NOCOUNT ON;
	SELECT ScheduleID, StartTime, FinishTime, EmployeeID, DayOfWeek FROM Schedule WHERE (NOT (EmployeeID IS NULL)) AND (NOT (DayOfWeek IS NULL));
GO



IF EXISTS (SELECT * FROM sysobjects WHERE name = 'HRAddEmployeeTime' AND user_name(uid) = 'dbo')
	DROP PROCEDURE [dbo].[HRAddEmployeeTime];
GO

CREATE PROCEDURE [dbo].[HRAddEmployeeTime]
(
	@StartTime int,
	@FinishTime int,
	@EmployeeID int,
	@DayOfWeek tinyint
)
AS
	SET NOCOUNT OFF;
	INSERT INTO Schedule(StartTime, FinishTime, EmployeeID, DayOfWeek, Busy) VALUES (@StartTime, @FinishTime, @EmployeeID, @DayOfWeek, 1);
	SELECT ScheduleID, StartTime, FinishTime, EmployeeID, DayOfWeek FROM Schedule WHERE (ScheduleID = @@IDENTITY);
GO



IF EXISTS (SELECT * FROM sysobjects WHERE name = 'HRUpdateEmployeeTime' AND user_name(uid) = 'dbo')
	DROP PROCEDURE [dbo].[HRUpdateEmployeeTime];
GO

CREATE PROCEDURE [dbo].[HRUpdateEmployeeTime]
(
	@StartTime int,
	@FinishTime int,
	@EmployeeID int,
	@DayOfWeek tinyint,
	@Original_ScheduleID int,
	@ScheduleID int
)
AS
	SET NOCOUNT OFF;
	UPDATE Schedule SET StartTime = @StartTime, FinishTime = @FinishTime, EmployeeID = @EmployeeID, DayOfWeek = @DayOfWeek WHERE (ScheduleID = @Original_ScheduleID);
	SELECT ScheduleID, StartTime, FinishTime, EmployeeID, DayOfWeek FROM Schedule WHERE (ScheduleID = @ScheduleID);
GO



IF EXISTS (SELECT * FROM sysobjects WHERE name = 'HRDeleteEmployeeTime' AND user_name(uid) = 'dbo')
	DROP PROCEDURE [dbo].[HRDeleteEmployeeTime];
GO

CREATE PROCEDURE [dbo].[HRDeleteEmployeeTime]
(
	@Original_ScheduleID int
)
AS
	SET NOCOUNT OFF;
	DELETE FROM Schedule WHERE (ScheduleID = @Original_ScheduleID);
GO

















----------------------- BusyTimes STORED PROCES -------------------------------------------------
IF EXISTS (SELECT * FROM sysobjects WHERE name = 'HRGetBusyTimes' AND user_name(uid) = 'dbo')
	DROP PROCEDURE [dbo].[HRGetBusyTimes];
GO

CREATE PROCEDURE [dbo].[HRGetBusyTimes]
	@EmployeeID INT,
	@StartTime  DATETIME,
	@EndTime	DATETIME,
	@Busy		BIT
AS
	SET NOCOUNT ON;
	IF (@EmployeeID IS NULL)
	BEGIN
		IF ((@StartTime IS NULL) OR (@EndTime IS NULL))
			SELECT ScheduleID, Busy, StartTime, FinishTime, ExactDate, Descript, EmployeeID FROM Schedule WHERE (NOT (EmployeeID IS NULL)) AND (NOT (ExactDate IS NULL)) AND ((Busy=@Busy) OR (@Busy IS NULL));
		ELSE
			SELECT ScheduleID, Busy, StartTime, FinishTime, ExactDate, Descript, EmployeeID FROM Schedule WHERE (NOT (EmployeeID IS NULL)) AND ((DATEADD(mi,StartTime,ExactDate) between @StartTime and @EndTime) OR (DATEADD(mi,FinishTime,ExactDate) between @StartTime and @EndTime)) AND ((Busy=@Busy) OR (@Busy IS NULL));
	END
	ELSE
	BEGIN
		IF ((@StartTime IS NULL) OR (@EndTime IS NULL))
			SELECT ScheduleID, Busy, StartTime, FinishTime, ExactDate, Descript, EmployeeID FROM Schedule WHERE (EmployeeID = @EmployeeID) AND (NOT (ExactDate IS NULL)) AND ((Busy=@Busy) OR (@Busy IS NULL));
		ELSE
			SELECT ScheduleID, Busy, StartTime, FinishTime, ExactDate, Descript, EmployeeID FROM Schedule WHERE (EmployeeID = @EmployeeID) AND ((DATEADD(mi,StartTime,ExactDate) between @StartTime and @EndTime) OR (DATEADD(mi,FinishTime,ExactDate) between @StartTime and @EndTime)) AND ((Busy=@Busy) OR (@Busy IS NULL));
	END
GO



IF EXISTS (SELECT * FROM sysobjects WHERE name = 'HRAddBusyTime' AND user_name(uid) = 'dbo')
	DROP PROCEDURE [dbo].[HRAddBusyTime];
GO

CREATE PROCEDURE [dbo].[HRAddBusyTime]
(
	@Busy bit,
	@StartTime int,
	@FinishTime int,
	@ExactDate datetime,
	@Descript nvarchar(512),
	@EmployeeID int
)
AS
	SET NOCOUNT OFF;
	INSERT INTO Schedule(Busy, StartTime, FinishTime, ExactDate, Descript, EmployeeID) VALUES (@Busy, @StartTime, @FinishTime, @ExactDate, @Descript, @EmployeeID);
	SELECT ScheduleID, Busy, StartTime, FinishTime, ExactDate, Descript, EmployeeID FROM Schedule WHERE (ScheduleID = @@IDENTITY);
GO



IF EXISTS (SELECT * FROM sysobjects WHERE name = 'HRUpdateBusyTime' AND user_name(uid) = 'dbo')
	DROP PROCEDURE [dbo].[HRUpdateBusyTime];
GO

CREATE PROCEDURE [dbo].[HRUpdateBusyTime]
(
	@Busy bit,
	@StartTime int,
	@FinishTime int,
	@ExactDate datetime,
	@Descript nvarchar(512),
	@EmployeeID int,
	@Original_ScheduleID int,
	@ScheduleID int
)
AS
	SET NOCOUNT OFF;
	UPDATE Schedule SET Busy=@Busy, StartTime = @StartTime, FinishTime = @FinishTime, ExactDate = @ExactDate, Descript = @Descript, EmployeeID = @EmployeeID WHERE (ScheduleID = @Original_ScheduleID);
	SELECT ScheduleID, Busy, StartTime, FinishTime, ExactDate, Descript, EmployeeID FROM Schedule WHERE (ScheduleID = @ScheduleID);
GO



IF EXISTS (SELECT * FROM sysobjects WHERE name = 'HRDeleteBusyTime' AND user_name(uid) = 'dbo')
	DROP PROCEDURE [dbo].[HRDeleteBusyTime];
GO

CREATE PROCEDURE [dbo].[HRDeleteBusyTime]
(
	@Original_ScheduleID int
)
AS
	SET NOCOUNT OFF;
	DELETE FROM Schedule WHERE (ScheduleID = @Original_ScheduleID);
GO


